﻿CREATE FUNCTION [dbo].[CollectionInfo](
	@LoanID int,
	@UsixID varchar(8),
	@CompanyID int,
	@DaysTillPayoff int
)RETURNS TABLE RETURN
SELECT     LoanID, [Customer #], ApplID, SSN, VIN, [G_L Date], [Open Date], Name, Address, City, St, [Zip Code], [Home Phone #], [Work Phone #], [Car Year], 
                      Make, Model, Mileage, [Pymnt Schedule], [# of Payments], [Payment Amount], [1st_ Payment Date], FirstPaymentDateCalc, [Last Payment Date], 
                      [Next Payment Date], [# Pmts Remaining], [Status Code], Loan#_Comments, [Investor Client], [Dealer Code], Dealer, [Joint Name], [Joint Address], 
                      [Joint City], [Joint State], [Joint Zipcode], [Joint SSN], [Joint H Phone#], [Joint W Phone#], [Primary Cell#], [Joint Cell#], [NSF Balance], [Late Chg Bal], 
                      [Apply Late Fee], [Bankruptcy Filed Y_N?], [Bankruptcy Type 07_13?], [Total Principal], DPD, DSLP, PaymentsMade, [Final Repo Date], 
                      [Title Received Y_N?], [Amt_ Fin__Disc_ Amt], [Partial Payment], [# Pmts RemainingM], [# of PaymentsM], PaymentsMadeM, NDay, CompanyID_Raw, 
                      PerDiem, PastDue, DPD30, DPD60, DPD90, PayOff_TP, PayOff_Calc, StatusID_BuyBack, Status_BuyBack, LocalTime, TimeZone, APR, DealerPhone, 
                      LateCharges, LatePmtsCount, LateBalance, StatusID_Collection, CollectorName, CollectorShort, UsixID, CollectorExtension, CompanyFullName, 
                      NSF_Date, NSF_Count, NSF_Amount, PastDuePayments, HasDeferment,
@DaysTillPayOff  AS PODays,
ISNULL([Total Principal], 0) + ISNULL((DSLP+@DaysTillPayOff) * PerDiem, 0) + ISNULL(LateCharges, 0) + ISNULL([NSF Balance], 0) AS PayOff
FROM         v_CollectionInfo
WHERE LoanID = @LoanID OR (UsixID = @UsixID AND CompanyID_Raw = @CompanyID)
